package cz.uhk.max.export.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.lang.reflect.Method;
import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import cz.uhk.max.exceptions.ExportExcelException;
import cz.uhk.max.exceptions.ExportException;
import cz.uhk.max.matrix.Matrix;
import cz.uhk.max.util.DateUtils;

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class JXLExcelGenerator<T> implements ExcelGenerator<T> {
	
	private Workbook template = null;
	private int startRow = 0;
	private boolean headersCreated;
	
	public void loadTemplate(InputStream in, int startRow, boolean headersCreated) 
		throws ExportExcelException {
		try {
			template = Workbook.getWorkbook(in);
			this.startRow = startRow;
			this.headersCreated = headersCreated;
		} catch (Throwable t) {
			throw new ExportExcelException(t);
		}
	}
	
	public void loadTemplate(String filePath, int startRow, boolean headersCreated) 
		throws ExportExcelException {
		try {
			loadTemplate(new FileInputStream(filePath), startRow, headersCreated);
		} catch (FileNotFoundException e) {
			throw new ExportExcelException(e);
		}
	}
	
	public void loadTemplate(File file, int startRow, boolean headersCreated) 
		throws ExportExcelException {
		try {
			loadTemplate(new FileInputStream(file), startRow, headersCreated);
		} catch (FileNotFoundException e) {
			throw new ExportExcelException(e);
		}
	}
	
	@SuppressWarnings("unchecked")
	public void generateExcel(List<T> data, String sheetName, OutputStream out) 
		throws ExportExcelException {
		
		if (data.size() > 0) {
		
			Class clazz = data.get(0).getClass();
			
			WritableWorkbook workBook = null;
			WritableSheet sheet = null;		
			try {
				if (template == null) {
					workBook = Workbook.createWorkbook(out);
					sheet = workBook.createSheet(sheetName, 0);
				} else {
					workBook = Workbook.createWorkbook(out, template);
					sheet = workBook.getSheet(0);
					sheet.setName(sheetName);
				}
			} catch (IOException e) {
				throw new ExportExcelException(e);
			}		
			
			if (!headersCreated) {
				ExcelColumn[] excelColumns = getExcelColumns(clazz);
				
				for (ExcelColumn excelColumn: excelColumns) {			
					Label label = new Label(
							excelColumn.order(), 0, excelColumn.columnHeaderName());
					try {
						sheet.addCell(label);
					} catch (Throwable t) {
						throw new ExportExcelException(t);
					}
				}
			}
			
			String[] getters = getGetters(clazz);
			
			if (!headersCreated) {
				startRow = 1;
			} else {
				if (startRow > 0) startRow--;
			}
			
			for (int i = 0; i < data.size(); i++) {
				
				T object = data.get(i);
				for (int j = 0; j < getters.length; j++) {
					try {
						Object value = clazz.getMethod(getters[j]).invoke(object);
						sheet.addCell(getCell(j, i + startRow, value));
					} catch (Throwable t) {
						throw new ExportExcelException(t);
					}
				}			
				
			}
			
			try {
				workBook.write();
				workBook.close();
				out.close();
			} catch (Throwable t) {
				throw new ExportExcelException(t);
			}
		}		
		
	}
	
		
//	// metoda pro export sestavy provizi
//	@SuppressWarnings("unchecked")
//	public void generateExcel(List<T> data, String sheetName, OutputStream out, CommissionFilter filtr, T60Agent agent, ArrayList pomocne) 
//		throws ExportExcelException {
//		
//		if (data.size() > 0) {
//		
//			Class clazz = data.get(0).getClass();
//			
//			WritableWorkbook workBook = null;
//			WritableSheet sheet = null;		
//			try {
//				if (template == null) {
//					workBook = Workbook.createWorkbook(out);
//					sheet = workBook.createSheet(sheetName, 0);
//				} else {
//					workBook = Workbook.createWorkbook(out, template);
//					sheet = workBook.getSheet(0);
//					sheet.setName(sheetName);
//				}
//			} catch (IOException e) {
//				throw new ExportExcelException(e);
//			}		
//			
//			if (!headersCreated) {
//				ExcelColumn[] excelColumns = getExcelColumns(clazz);
//				
//				for (int i = 0; i < 5; i++) {
//					ExcelColumn excelColumn = excelColumns[i];
//					
//					Label label = new Label(
//							excelColumn.order(), 0, excelColumn.columnHeaderName());
//					try {
//						sheet.addCell(label);
//					} catch (Throwable t) {
//						throw new ExportExcelException(t);
//					}
//				}
//			}
//			
//			String[] getters = getGetters(clazz);
//			
//			if (!headersCreated) {
//				startRow = 1;
//			} else {
//				if (startRow > 0) startRow--;
//			}
//			
//			Integer radekSumaCelkem = data.size() + startRow + 1;
//			
//			for (int i = 0; i < data.size(); i++) {
//				
//				T object = data.get(i);
//				for (int j = 0; j < 5; j++) {
//					try {
//						Object value = clazz.getMethod(getters[j]).invoke(object);
//						sheet.addCell(getCell(j, i + startRow, value));
//					} catch (Throwable t) {
//						throw new ExportExcelException(t);
//					}
//				}			
//			}
//			
//			T object = data.get(0);
//			try {
//				Object jmeno = clazz.getMethod(getters[5]).invoke(object);
//				sheet.addCell(getCell(1, 2, jmeno));
//				
//				Object prijmeni = clazz.getMethod(getters[6]).invoke(object);
//				sheet.addCell(getCell(1, 3, prijmeni));
//				
//				Object agCislo = clazz.getMethod(getters[7]).invoke(object);
//				sheet.addCell(getCell(1, 4, agCislo));
//
//				String vztahOd = DateUtils.date2String(agent.getT60SmluvniVztahOd(), "dd.MM.yyyy");
//				Object vztahDo = DateUtils.date2String(agent.getT60SmluvniVztahDo(), "dd.MM.yyyy");
//				Object filtrOd = DateUtils.date2String(filtr.getDatumUzaverkyOd(), "dd.MM.yyyy");
//				Object filtrDo = DateUtils.date2String(filtr.getDatumUzaverkyDo(), "dd.MM.yyyy");
//				
//				if (vztahOd != null) sheet.addCell(getCell(3, 2, vztahOd));
//				if (vztahDo != null) sheet.addCell(getCell(4, 2, vztahDo));
//				if (filtrOd != null) sheet.addCell(getCell(3, 3, filtrOd));
//				if (filtrDo != null) sheet.addCell(getCell(4, 3, filtrDo));
//				
//			} catch (Throwable t) {
//				throw new ExportExcelException(t);
//			}
//			Integer poziceMesic = 6;
//			sheet.insertRow(6);
//			
//			for (int i = 0; i < pomocne.size() - 1; i+=4) {
//				String mesic = null;
//				try {
//					
//					switch((Integer)pomocne.get(i+1)) {
//			        case 1:
//			        	mesic = "leden " + pomocne.get(i + 3);
//			        	break;
//			        case 2:
//			        	mesic = "únor " + pomocne.get(i + 3);
//			        	break;
//			        case 3:
//			        	mesic = "březen " + pomocne.get(i + 3);
//			        	break;
//			        case 4:
//			        	mesic = "duben " + pomocne.get(i + 3);
//			        	break;
//			        case 5:
//			        	mesic = "květen " + pomocne.get(i + 3);
//			        	break;
//			        case 6:
//			        	mesic = "červen " + pomocne.get(i + 3);
//			        	break;
//			        case 7:
//			        	mesic = "červenec " + pomocne.get(i + 3);
//			        	break;
//			        case 8:
//			        	mesic = "srpen " + pomocne.get(i + 3);
//			        	break;
//			        case 9:
//			        	mesic = "září " + pomocne.get(i + 3);
//			        	break;
//			        case 10:
//			        	mesic = "říjen " + pomocne.get(i + 3);
//			        	break;
//			        case 11:
//			        	mesic = "listopad " + pomocne.get(i + 3);
//			        	break;
//			        case 12:
//			        	mesic = "prosinec " + pomocne.get(i + 3);
//			        	break;
//					}
//					
//					sheet.addCell(getCell(0, poziceMesic, mesic));
//					poziceMesic = poziceMesic + (Integer) pomocne.get(i)+1;
//					sheet.insertRow(poziceMesic);
//					radekSumaCelkem += 2;
//					
//				} catch (RowsExceededException e) {
//					e.printStackTrace();
//				} catch (WriteException e) {
//					e.printStackTrace();
//				}
//			}
//			
//			Integer poziceSuma = 5;
//			for (int j = 0; j < pomocne.size() - 1; j+=4) {
//				poziceSuma = poziceSuma + (Integer) pomocne.get(j) + 2;
//				sheet.insertRow(poziceSuma);
//				try {
//					sheet.addCell(getCell(3, poziceSuma, pomocne.get(j + 2)));
//				} catch (RowsExceededException e) {
//					e.printStackTrace();
//				} catch (WriteException e) {
//					e.printStackTrace();
//				}
//			}
//			
//			try {
//				sheet.addCell(getCell(2,radekSumaCelkem,"Celkem"));
//				sheet.addCell(getCell(3,radekSumaCelkem,pomocne.get(pomocne.size()-1)));
//			} catch (RowsExceededException e) {
//				e.printStackTrace();
//			} catch (WriteException e) {
//				e.printStackTrace();
//			}	
//			
//			try {
//				workBook.write();
//				workBook.close();
//				out.close();
//			} catch (Throwable t) {
//				throw new ExportExcelException(t);
//			}
//		}		
//		
//	}
	
	// metoda pro export prehledu oblastnich reditelstvi
	@SuppressWarnings("unchecked")
	public void generateExcel(List<T> data, String sheetName, OutputStream out, ArrayList pomocne) 
		throws ExportExcelException {
		
		if (data.size() > 0) {
		
			Class clazz = data.get(0).getClass();
			
			WritableWorkbook workBook = null;
			WritableSheet sheet = null;		
			try {
				if (template == null) {
					workBook = Workbook.createWorkbook(out);
					sheet = workBook.createSheet(sheetName, 0);
				} else {
					workBook = Workbook.createWorkbook(out, template);
					sheet = workBook.getSheet(0);
					sheet.setName(sheetName);
				}
			} catch (IOException e) {
				throw new ExportExcelException(e);
			}		
			
			if (!headersCreated) {
				ExcelColumn[] excelColumns = getExcelColumns(clazz);
				
				for (int i = 0; i < 7; i++) {
					ExcelColumn excelColumn = excelColumns[i];
					
					Label label = new Label(
							excelColumn.order(), 0, excelColumn.columnHeaderName());
					try {
						sheet.addCell(label);
					} catch (Throwable t) {
						throw new ExportExcelException(t);
					}
				}
			}
			
			String[] getters = getGetters(clazz);
			
			startRow = 1;
			
			Integer radekSumaCelkem = data.size() + startRow + 1;
			
			for (int i = 0; i < data.size(); i++) {
				
				T object = data.get(i);
				for (int j = 0; j < 7; j++) {
					try {
						Object value = clazz.getMethod(getters[j]).invoke(object);
						sheet.addCell(getCell(j, i + startRow, value));
					} catch (Throwable t) {
						throw new ExportExcelException(t);
					}
				}			
			}
			
			T object = data.get(0);
			
			Integer poziceMesic = 1;
			sheet.insertRow(1);
			
			for (int i = 0; i < pomocne.size() - 1; i+=4) {
				String mesic = null;
				try {
					
					switch((Integer)pomocne.get(i+1)) {
					case 0:
						mesic = "neukončeno";
						break;
					case 1:
			        	mesic = "leden " + pomocne.get(i + 3);
			        	break;
			        case 2:
			        	mesic = "únor " + pomocne.get(i + 3);
			        	break;
			        case 3:
			        	mesic = "březen " + pomocne.get(i + 3);
			        	break;
			        case 4:
			        	mesic = "duben " + pomocne.get(i + 3);
			        	break;
			        case 5:
			        	mesic = "květen " + pomocne.get(i + 3);
			        	break;
			        case 6:
			        	mesic = "červen " + pomocne.get(i + 3);
			        	break;
			        case 7:
			        	mesic = "červenec " + pomocne.get(i + 3);
			        	break;
			        case 8:
			        	mesic = "srpen " + pomocne.get(i + 3);
			        	break;
			        case 9:
			        	mesic = "září " + pomocne.get(i + 3);
			        	break;
			        case 10:
			        	mesic = "říjen " + pomocne.get(i + 3);
			        	break;
			        case 11:
			        	mesic = "listopad " + pomocne.get(i + 3);
			        	break;
			        case 12:
			        	mesic = "prosinec " + pomocne.get(i + 3);
			        	break;
					}
					
					sheet.addCell(getCell(0, poziceMesic, mesic));
					poziceMesic = poziceMesic + (Integer) pomocne.get(i) + 1;
					sheet.insertRow(poziceMesic);
					radekSumaCelkem += 2;
					
				} catch (RowsExceededException e) {
					e.printStackTrace();
				} catch (WriteException e) {
					e.printStackTrace();
				}
			}
			
			Integer poziceSuma = 0;
			for (int j = 0; j < pomocne.size() - 1; j+=4) {
				poziceSuma = poziceSuma + (Integer) pomocne.get(j) + 2;
				sheet.insertRow(poziceSuma);
				try {
					sheet.addCell(getCell(5, poziceSuma, pomocne.get(j + 2)));
				} catch (RowsExceededException e) {
					e.printStackTrace();
				} catch (WriteException e) {
					e.printStackTrace();
				}
			}
			
			try {
				sheet.addCell(getCell(4,radekSumaCelkem,"Celkem"));
				sheet.addCell(getCell(5,radekSumaCelkem,pomocne.get(pomocne.size()-1)));
			} catch (RowsExceededException e) {
				e.printStackTrace();
			} catch (WriteException e) {
				e.printStackTrace();
			}	
			
			try {
				workBook.write();
				workBook.close();
				out.close();
			} catch (Throwable t) {
				throw new ExportExcelException(t);
			}
		}		
		
	}
	
	public void generateExcel(List<T> data, String sheetName, String filePath) 
		throws ExportExcelException {
		try {
			generateExcel(data, sheetName, new FileOutputStream(filePath));
		} catch (FileNotFoundException e) {
			throw new ExportExcelException(e);
		}
	}

	public void generateExcel(List<T> data, String sheetName, File file) 
		throws ExportExcelException {
		try {
			generateExcel(data, sheetName, new FileOutputStream(file));
		} catch (FileNotFoundException e) {
			throw new ExportExcelException(e);
		}
	}
	

//	public void generateExcel(List<T> data, String sheetName, File file, CommissionFilter filtr, T60Agent agent, ArrayList pomocne) 
//		throws ExportExcelException {
//		try {
//			generateExcel(data, sheetName, new FileOutputStream(file), filtr, agent, pomocne);
//		} catch (FileNotFoundException e) {
//			throw new ExportExcelException(e);
//		}
//	}
	
	public void generateExcel(List<T> data, String sheetName, File file, ArrayList pomocne) 
		throws ExportExcelException {
		try {
			generateExcel(data, sheetName, new FileOutputStream(file), pomocne);
		} catch (FileNotFoundException e) {
			throw new ExportExcelException(e);
		}
	}
	
	private int getColumnCount(Class<T> clazz) {
		
		int count = 0;		
		for (Method method: clazz.getMethods()) {
			if (method.isAnnotationPresent(ExcelColumn.class)) {
				count++;
			}
		}		
		return count;
	}
	
	private ExcelColumn[] getExcelColumns(Class<T> clazz) {
		
		int columnCount = getColumnCount(clazz);
		ExcelColumn[] annotations = new ExcelColumn[columnCount];
		
		for (Method method: clazz.getMethods()) {
			if (method.isAnnotationPresent(ExcelColumn.class)) {
				ExcelColumn excelColumn = method.getAnnotation(ExcelColumn.class);
				int order = excelColumn.order();
				annotations[order] = excelColumn;
			}
		}
		
		return annotations;
		
	}
	
	private String[] getGetters(Class<T> clazz) {
		
		int columnCount = getColumnCount(clazz);
		String[] getters = new String[columnCount];
		
		for (Method method: clazz.getMethods()) {
			if (method.isAnnotationPresent(ExcelColumn.class)) {
				ExcelColumn excelColumn = method.getAnnotation(ExcelColumn.class);
				int order = excelColumn.order();
				getters[order] = method.getName();
			}
		}
		
		return getters;
		
	}

	private WritableCell getCell(int column, int row, Object value) {
		if (value == null) return new Label(column, row, "");
		if (value instanceof Number) {
			return new jxl.write.Number(
					column, row, ((Number) value).doubleValue());
		} else {
			String stringValue = null;
			if (value instanceof Date) {
				stringValue = DateUtils.date2String((Date) value, "dd.MM.yyyy");
			} else if (value instanceof String) {
				stringValue = (String) value;
			} else {
				stringValue = String.valueOf(value);
			}
			return new Label(column, row, stringValue);
		}
	}
}
